package dapps.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;

import dapps.entity.user.User;

@Repository
public class UserJdbcDao {

	private static final String QUERY_USER_BY_ID = "select id,email,name,password,username,uorder,type_id,code,address,contacter,contacttel,fax,mobile,zip,district_id,iscity  from users where id=?";

	private static final String[] userfields = { "id", "email", "name",
			"password", "username", "code", "address", "contacter",
			"contacttel", "fax", "mobile", "zip", 
			"iscity" };

	private SimpleJdbcTemplate jdbcTemplate;

	@Resource
	public void setDataSourceRemote(DataSource dataSource) {
		jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	ParameterizedRowMapper<User> mapper = new ParameterizedRowMapper<User>() {
		public User mapRow(ResultSet rs, int rowNum) throws SQLException {
			User actor = new User();
			actor.setId(rs.getLong("id"));
			actor.setUsername(rs.getString("username"));
			actor.setName(rs.getString("name"));
			return actor;
		}
	};

	private static final String Count_USER_BY_ID = "select count(id)  from users where id=?";

	/**
	 * 查询单个对象.
	 */
	public User queryObject(Long id) {
		return (User) this.jdbcTemplate.queryForObject(QUERY_USER_BY_ID,
				mapper, id);
	}

	public int countuser(Long id) {
		return this.jdbcTemplate.queryForInt(Count_USER_BY_ID, id);
	}
	private static final String Count_role = "select count(user_id)  from users_roles where user_id=? and role_id=?";
	private static final String insertcompanyrole ="insert into users_roles(user_id,role_id) values (?,3)";
	private static final String deleteuser ="delete from  users where id=?";
	
	public int countuserrole(Long id) {
		return this.jdbcTemplate.queryForInt(Count_role, id,3);
	}
	
	//update user
	public void updateUser(User user) {
		BeanPropertySqlParameterSource su = new BeanPropertySqlParameterSource(
				user);
		if (this.countuser(user.getId()) > 0) {
			jdbcTemplate.update(getUpdateSql(su,user), su);
		} else {
			jdbcTemplate.update(getCreateSql(su,user), su);			
		}
		if(this.countuserrole(user.getId())==0) jdbcTemplate.update(insertcompanyrole, user.getId());
	}
	
	//delete
	public void delUser(Long id) {
		if (this.countuser(id) > 0) {
			this.jdbcTemplate.update(deleteuser,id);
		} 
	}
	public String getUpdateSql(BeanPropertySqlParameterSource su,User user) {

		String sql = "update users set ";
		for (int i = 1; i < userfields.length; i++) {
			if (su.getValue(userfields[i]) != null) {
				sql += userfields[i] + "=:" + userfields[i] + ",";
			}
		}
		Long type_id=0L;
		Long district_id=0L;
		if(user.getType()!=null){
			type_id = user.getType().getId();
			if(type_id>0L){sql +="type_id="+type_id+",";}
		}
		if(user.getDistrict()!=null){
			district_id = user.getDistrict().getId();
			if(district_id>0L){sql +="district_id="+district_id+",";}
		}
		sql += "id=:id where id=:id";
		return sql;
	}

	public String getCreateSql(BeanPropertySqlParameterSource su,User user) {		
		String sql = "insert into users ( ";
		for (int i = 1; i < userfields.length; i++) {
			if (su.getValue(userfields[i]) != null) {
				sql += userfields[i] + ",";
			}
		}
		Long type_id=0L;
		Long district_id=0L;
		if(user.getType()!=null){
			type_id = user.getType().getId();
			if(type_id>0L){sql +="type_id,";}
		}
		if(user.getDistrict()!=null){
			district_id = user.getDistrict().getId();
			if(district_id>0L){sql +="district_id,";}
		}
		sql += "id) values (";
		for (int i = 1; i < userfields.length; i++) {
			if (su.getValue(userfields[i]) != null) {
				sql += ":" + userfields[i] + ",";
			}
		}
		if(type_id>0L){
			sql += +type_id+",";
		}
		if(district_id>0L){
			sql += +district_id+",";
		}
		sql += ":id)";
		return sql;
	}
}
